package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import javax.swing.Box.Filler;
import javax.swing.DebugGraphics;
import javax.swing.plaf.basic.BasicInternalFrameTitlePane.RestoreAction;

import com.entity.BoardDTO;
import com.entity.PageTO;

public class BoardDAO {

	DataSource dataFactory;
	
	public BoardDAO() {
		
		try {
			Context ctx = new InitialContext();
			dataFactory = (DataSource) ctx.lookup("java:comp/env/jdbc/Oracle11g");
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public ArrayList<BoardDTO> list(){
		ArrayList<BoardDTO> list = new ArrayList<BoardDTO>();
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = dataFactory.getConnection();
			
			String query = "SELECT num, author, title, content, to_char(writeday, 'YYYY/MM/DD') writeday, readcnt, reqRoot, reqStep, repIndent From Board order by reqRoot desc, reqStep asc";
			
			
			pstmt = conn.prepareStatement(query);
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				int num = rs.getInt("num");
				String author = rs.getString("author");
				String title = rs.getString("title");
				String content = rs.getString("content");
				String writeday = rs.getString("writeday");
				int readcnt = rs.getInt("readcnt");
				int reqRoot = rs.getInt("reqRoot");
				int reqStep = rs.getInt("reqStep");
				int repIndent = rs.getInt("repIndent");
				
				BoardDTO data = new BoardDTO();
				data.setNum(num);
				data.setAuthor(author);
				data.setTitle(title);
				data.setContent(content);
				data.setWriteDay(writeday);
				data.setReadcnt(readcnt);
				data.setReqRoot(reqRoot);
				data.setReqStep(reqStep);
				data.setRepIndent(repIndent);
				
				list.add(data);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}		
		finally
		{
			try {
				if(rs!=null)rs.close();
				if(pstmt!=null)pstmt.close();
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return list;
	}
	
	public void write(String _title, String _author, String _content)
	{
		Connection con= null;
		PreparedStatement pstmt = null;
		
		try {
			
			con = dataFactory.getConnection();
			String query = "Insert into board(num, title, author, content, readcnt, reqRoot, reqStep, repIndent) values(board_seq.nextval, ?, ?, ?, 0, board_seq.currval, 0, 0)";
			
			pstmt = con.prepareStatement(query);
			
			pstmt.setString(1, _title);
			pstmt.setString(2, _author);
			pstmt.setString(3, _content);
			
			int n = pstmt.executeUpdate();
			
			
		} catch (SQLException e) {			
			e.printStackTrace();
		}
		finally{
			try {
				if(pstmt!=null) pstmt.close();
				if(con!=null) con.close();
			} catch (SQLException e) {		
				e.printStackTrace();
			}
		}
	}

	public void readCount(String _num)
	{
		Connection con = null;
		
		PreparedStatement pstmt = null;
		
		try {			
			con = dataFactory.getConnection();			
			String query = "Update board set readcnt = readcnt + 1 where num = " + _num;
			
			pstmt = con.prepareStatement(query);
			
			int n = pstmt.executeUpdate();
			
		} catch (Exception e) {			
			e.printStackTrace();
		}
		finally
		{
			try {
				if (pstmt != null) pstmt.close();
				if (con != null) con.close();				
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

	public BoardDTO retrieve(String _num)
	{
		readCount(_num);
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BoardDTO data = new BoardDTO();
		
		try {
			con = dataFactory.getConnection();
			
			String query = "SELECT * from board where num = ?";
			pstmt = con.prepareStatement(query);
			pstmt.setInt(1, Integer.parseInt(_num));
			
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				int num = rs.getInt("num");
				String title = rs.getString("title");
				String author = rs.getString("author");
				String content = rs.getString("content");
				String writeday = rs.getString("writeday");
				int readcnt = rs.getInt("readcnt");
				
				data.setNum(num);
				data.setTitle(title);
				data.setAuthor(author);
				data.setContent(content);
				data.setWriteDay(writeday);
				data.setReadcnt(readcnt);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			try {
				if(rs != null)rs.close();
				if(pstmt != null)pstmt.close();
				if(con!=null)con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return data;
	}

	public void update(String _num, String _title, String _author, String _content)
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try {
			
			con = dataFactory.getConnection();
			String query = "Update board set title = ? , author = ? , content = ? where num = ?";
			
			pstmt = con.prepareStatement(query);
			
			pstmt.setString(1, _title);
			pstmt.setString(2, _author);
			pstmt.setString(3, _content);
			pstmt.setInt(4, Integer.parseInt(_num));
			
			int n = pstmt.executeUpdate();
			
		} catch (SQLException e) {			
			e.printStackTrace();
		}
		finally
		{
			try {
				if(pstmt != null)pstmt.close();
				if(con != null)con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

	public void delete(String _num)
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try {
			con = dataFactory.getConnection();
			
			String query = "Delete from board where num = ?";
			
			pstmt= con.prepareStatement(query);
			pstmt.setInt(1, Integer.parseInt(_num));
			int n = pstmt.executeUpdate();
			
		} catch (SQLException e) {			
			e.printStackTrace();
		}
		finally{
			try {
				if(pstmt != null)pstmt.close();
				if(con != null)con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public ArrayList<BoardDTO> search(String _searchName, String _searchValue)
	{
		ArrayList<BoardDTO> list = new ArrayList<>();
		
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			
			con = dataFactory.getConnection();
			
			String query = "Select num, author, title, content, to_char(writeday, 'YYYY/MM/DD') writeday, readcnt from board where 1 = 1";
			
			if(_searchName.equals("title"))
				query += " and title like ?";
			else
				query += " and author like ?";
			
			pstmt = con.prepareStatement(query);
			pstmt.setString(1, "%"+_searchValue+"%");			
			
			rs = pstmt.executeQuery();
			
			while (rs.next()) 
			{
				int num = rs.getInt("num");
				String author = rs.getString("author");
				String title = rs.getString("title");
				String content = rs.getString("content");
				String writeday = rs.getString("writeday");
				int readcnt = rs.getInt("readcnt");
				
				BoardDTO data = new BoardDTO();
				data.setNum(num);
				data.setAuthor(author);
				data.setTitle(title);
				data.setContent(content);
				data.setWriteDay(writeday);
				data.setReadcnt(readcnt);
				
				list.add(data);
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		finally
		{
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(con != null) con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return list;
	}

	public BoardDTO replyui(String _num)
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BoardDTO data = new BoardDTO();
		
		try {
			con = dataFactory.getConnection();
			
			String query = "Select * from board where num = ?";
			pstmt = con.prepareStatement(query);
			pstmt.setInt(1, Integer.parseInt(_num));
			
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				data.setNum(rs.getInt("num"));
				data.setTitle(rs.getString("title"));
				data.setAuthor(rs.getString("author"));
				data.setContent(rs.getString("content"));
				data.setWriteDay(rs.getString("writeday"));
				data.setReadcnt(rs.getInt("readcnt"));
				data.setReqRoot(rs.getInt("reqRoot"));
				data.setReqStep(rs.getInt("reqStep"));
				data.setRepIndent(rs.getInt("repIndent"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(con != null) con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return data;
	}
	
	public void makeReply(String _root, String _step)
	{		
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try {
			con =dataFactory.getConnection();
			
			String query = "Update board set reqStep = reqStep + 1 where reqRoot = ? and reqStep > ?";
			
			pstmt = con.prepareStatement(query);
			
			pstmt.setInt(1, Integer.parseInt(_root));
			pstmt.setInt(2, Integer.parseInt(_step));
			
			int n = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			try {
				if(pstmt != null)pstmt.close();
				if(con != null)con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void reply(String _num, String _title, String _author, String _content, String _reqRoot, String _reqStep, String _repIndent)
	{
		makeReply(_reqRoot, _reqStep);
		
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try {
			con = dataFactory.getConnection();
			
			String query = "Insert into board(num, title, author, content, reqRoot, reqStep, repIndent) Values(board_seq.nextVal, ?, ?, ?, ?, ?, ?)";
			
			pstmt = con.prepareStatement(query);
			
			pstmt.setString(1, _title);
			pstmt.setString(2, _author);
			pstmt.setString(3, _content);
			pstmt.setInt(4, Integer.parseInt(_reqRoot));
			pstmt.setInt(5, Integer.parseInt(_reqStep) + 1);
			pstmt.setInt(6, Integer.parseInt(_repIndent) + 1);
			
			System.out.println(_num + _title + _author + _content + _reqRoot + _reqStep + _repIndent);
			System.out.println(query);
			
			int n = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			try {
				if(pstmt != null) pstmt.close();
				if(con != null)con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public int totalCount()
	{
		int count = 0;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			con = dataFactory.getConnection();
			String query = "Select count(*) from board";
			
			pstmt = con.prepareStatement(query);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				count = rs.getInt(1);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(con != null) con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return count;
	}

	public PageTO page(int curPage)
	{
		PageTO to = new PageTO();
		int totalCount = totalCount();
		
		ArrayList<BoardDTO> list = new ArrayList<BoardDTO>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			con = dataFactory.getConnection();
			
			String query = "Select num, author, title, content, to_char(writeday, 'YYYY/MM/DD') writeday, readcnt, reqRoot, reqStep, repIndent from board order by reqRoot desc, reqStep asc";
			
			pstmt = con.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			
			rs = pstmt.executeQuery();
			
			int perPage = to.getPerPage();
			
			int skip = (curPage - 1) * perPage;
			
			if (skip > 0) {
				rs.absolute(skip);
			}
			
			for (int i = 0; i < perPage && rs.next(); i++) {
				
				int num = rs.getInt("num");
				String author = rs.getString("author");
				String title = rs.getString("title");
				String content = rs.getString("content");
				String writeday = rs.getString("writeday");
				int readcnt = rs.getInt("readcnt");
				int reqRoot = rs.getInt("reqRoot");
				int reqStep = rs.getInt("reqStep");
				int repIndent = rs.getInt("repIndent");
				
				BoardDTO data = new BoardDTO();
				data.setNum(num);
				data.setAuthor(author);
				data.setTitle(title);
				data.setContent(content);
				data.setWriteDay(writeday);
				data.setReadcnt(readcnt);
				data.setReqRoot(reqRoot);
				data.setReqStep(reqStep);
				data.setRepIndent(repIndent);
				
				list.add(data);
			}			
			
			to.setList(list);
			to.setTotalCount(totalCount);
			to.setCurPage(curPage);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			try {
				if(rs != null) rs.close();
				if(pstmt != null) pstmt.close();
				if(con != null)con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		return to;
	}
}
